# 2024/4/20 上午9:57 HFL
# 导包
import random
import traceback

import pymysql




# 定义函数
def db_mysql(sql: str):

    # 创建两个变量接收，数据库连接对象，游标对象
    con = None
    cur = None
    try:
        # 创建数据库连接对象
        con = pymysql.connect(
            host='211.103.136.244',
            port=7068,
            user='student',
            password='KDTX_student_202304',
            database='huike',
            charset='utf8'
        )
        # 创建游标对象
        cur = con.cursor()
        # 执行SQL
        cur.execute(sql)
        # 判断是查询语句 返回全部结果
        if sql.split(' ')[0].lower() == 'select':

            return cur.fetchall()
        else:
            # 数据库事务的提交
            con.commit()
            return f"受影响的行数:{cur.rowcount}"
    except:
        # 报错信息的收集
        traceback.print_exc()
        # 数据库事务的回滚
        if cur is not None:
            con.rollback()
    finally:
        if cur is not None:
            # 关闭游标对象
            cur.close()
        if con is not None:
            # 关闭数据库连接对象
            con.close()


if __name__ == '__main__':
    course_id = random.randint(10000,99999)
    sql = f"insert into tb_course (id, code,name,subject,price) values({course_id},'c01','python2022',6,2999);"
    # sql = "delete from tb_course where id='329422'"
    sql1 = f"select * from tb_course where  id ={course_id}"
    res = db_mysql(sql)
    print(res)
    res1 = db_mysql(sql1)
    print(res1[0][0])

